

BEGIN WORK;
DROP DATABASE IF EXISTS hopital;
CREATE DATABASE IF NOT EXISTS hopital;
USE hopital;
CREATE TABLE IF NOT EXISTS t_patients (
    c_id INT AUTO_INCREMENT PRIMARY KEY,
    c_nom VARCHAR(30) NOT NULL,
    c_prenom VARCHAR(30) NOT NULL,
    c_adresse VARCHAR(30) NOT NULL,
    c_telephone VARCHAR(30) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS t_admins (
    c_id INT AUTO_INCREMENT PRIMARY KEY,
    c_nom VARCHAR(30) NOT NULL,
    c_prenom VARCHAR(30) NOT NULL,
    c_email VARCHAR(30) NOT NULL UNIQUE,
    c_adresse VARCHAR(30) NOT NULL,
    c_telephone VARCHAR(30) NOT NULL UNIQUE,
    c_password VARCHAR(30) NOT NULL
);
CREATE TABLE IF NOT EXISTS t_docteurs (
    c_id INT AUTO_INCREMENT PRIMARY KEY,
    c_nom VARCHAR(30) NOT NULL,
    c_prenom VARCHAR(30) NOT NULL,
    c_email VARCHAR(30) NOT NULL UNIQUE,
    c_adresse VARCHAR(30) NOT NULL,
    c_telephone VARCHAR(30) NOT NULL UNIQUE,
    c_password VARCHAR(30) NOT NULL
);
CREATE TABLE IF NOT EXISTS t_infirmiers (
    c_id INT AUTO_INCREMENT PRIMARY KEY,
    c_nom VARCHAR(30) NOT NULL,
    c_prenom VARCHAR(30) NOT NULL,
    c_email VARCHAR(30) NOT NULL UNIQUE,
    c_adresse VARCHAR(30) NOT NULL,
    c_telephone VARCHAR(30) NOT NULL UNIQUE,
    c_password VARCHAR(30) NOT NULL
);
CREATE TABLE IF NOT EXISTS t_receptions (
    c_id INT AUTO_INCREMENT PRIMARY KEY,
    c_nom VARCHAR(30) NOT NULL,
    c_prenom VARCHAR(30) NOT NULL,
    c_email VARCHAR(30) NOT NULL UNIQUE,
    c_adresse VARCHAR(30) NOT NULL,
    c_telephone VARCHAR(30) NOT NULL UNIQUE,
    c_password VARCHAR(30) NOT NULL
);
CREATE TABLE IF NOT EXISTS t_comptables (
    c_id INT AUTO_INCREMENT PRIMARY KEY,
    c_nom VARCHAR(30) NOT NULL,
    c_prenom VARCHAR(30) NOT NULL,
    c_email VARCHAR(30) NOT NULL UNIQUE,
    c_adresse VARCHAR(30) NOT NULL,
    c_telephone VARCHAR(30) NOT NULL UNIQUE,
    c_password VARCHAR(30) NOT NULL
);

CREATE TABLE IF NOT EXISTS t_labos (
    c_id INT AUTO_INCREMENT PRIMARY KEY,
    c_nom VARCHAR(30) NOT NULL,
    c_prenom VARCHAR(30) NOT NULL,
    c_email VARCHAR(30) NOT NULL UNIQUE,
    c_adresse VARCHAR(30) NOT NULL,
    c_telephone VARCHAR(30) NOT NULL UNIQUE,
    c_password VARCHAR(30) NOT NULL
);

CREATE TABLE IF NOT EXISTS t_pharmaciens (
    c_id INT AUTO_INCREMENT PRIMARY KEY,
    c_nom VARCHAR(30) NOT NULL,
    c_prenom VARCHAR(30) NOT NULL,
    c_email VARCHAR(30) NOT NULL UNIQUE,
    c_adresse VARCHAR(30) NOT NULL,
    c_telephone VARCHAR(30) NOT NULL UNIQUE,
    c_password VARCHAR(30) NOT NULL
);

CREATE TABLE IF NOT EXISTS t_blocs (
    c_id INT AUTO_INCREMENT PRIMARY KEY,
    c_nom VARCHAR(30) NOT NULL
);

CREATE TABLE IF NOT EXISTS t_chambres (
    c_id INT AUTO_INCREMENT PRIMARY KEY,
    c_prix DOUBLE,
    c_bloc INT,
    c_status VARCHAR(30),
    CONSTRAINT fk_bloc FOREIGN KEY (c_bloc)
        REFERENCES t_blocs (c_id)
        ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE TABLE IF NOT EXISTS t_reservations (
    c_id INT PRIMARY KEY AUTO_INCREMENT,
    c_chambre INT,
    c_patient INT,
    c_datee DATE,
    c_dates DATE,
    c_etat VARCHAR(20),
    CONSTRAINT fk_patient_reservation FOREIGN KEY (c_patient)
        REFERENCES t_patients (c_id)
        ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT fk_chambre FOREIGN KEY (c_chambre)
        REFERENCES t_chambres (c_id)
        ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE TABLE IF NOT EXISTS t_consultations (
    c_id INT PRIMARY KEY AUTO_INCREMENT,
    c_docteur INT,
    c_patient INT,
    c_date DATE,
    c_maladie VARCHAR(100),
    c_ordonnance VARCHAR(100),
    c_remarque VARCHAR(100),
    c_etat VARCHAR(20),
    CONSTRAINT fk_docteur FOREIGN KEY (c_docteur)
        REFERENCES t_docteurs (c_id)
        ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT fk_patient_docteur FOREIGN KEY (c_patient)
        REFERENCES t_patients (c_id)
        ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE TABLE IF NOT EXISTS t_analyzes (
    c_id INT PRIMARY KEY AUTO_INCREMENT,
    c_labo INT,
    c_patient INT REFERENCES t_patients (c_id),
    c_date DATE,
    c_type VARCHAR(100),
    c_resultat VARCHAR(100),
    c_remarque VARCHAR(100),
    c_etat VARCHAR(20),
    CONSTRAINT fk_labo FOREIGN KEY (c_labo)
        REFERENCES t_labos (c_id)
        ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT fk_patient_labo FOREIGN KEY (c_patient)
        REFERENCES t_patients (c_id)
        ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE TABLE IF NOT EXISTS t_factures (
    c_id INT PRIMARY KEY AUTO_INCREMENT,
    c_comptable INT,
    c_patient INT REFERENCES t_patients (c_id),
    c_date DATE,
    c_etat VARCHAR(20),
    CONSTRAINT fk_comptable FOREIGN KEY (c_comptable)
        REFERENCES t_comptables (c_id)
        ON DELETE CASCADE ON UPDATE NO ACTION,
    CONSTRAINT fk_patient_comptable FOREIGN KEY (c_patient)
        REFERENCES t_patients (c_id)
        ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE TABLE IF NOT EXISTS t_detail_factures (
    c_facture INT,
    c_service VARCHAR(30),
    c_prix DOUBLE,
    CONSTRAINT fk_facture FOREIGN KEY (c_facture)
        REFERENCES t_factures (c_id)
        ON DELETE CASCADE ON UPDATE NO ACTION
);

CREATE VIEW v_users AS
    SELECT 
        *, 'ADMIN' AS TYPE
    FROM
        t_admins 
    UNION SELECT 
        *, 'DOCTEUR' AS TYPE
    FROM
        t_docteurs 
    UNION SELECT 
        *, 'COMPTABLE' AS TYPE
    FROM
        t_comptables 
    UNION SELECT 
        *, 'INFIRMIER' AS TYPE
    FROM
        t_infirmiers 
    UNION SELECT 
        *, 'RECEPTION' AS TYPE
    FROM
        t_receptions 
    UNION SELECT 
        *, 'LABO' AS TYPE
    FROM
        t_labos 
    UNION SELECT 
        *, 'PHARMACIEN' AS TYPE
    FROM
        t_pharmaciens;
CREATE VIEW v_services AS
    SELECT 
        c_etat, c_patient, 100 AS c_prix, 'ANALYZE' AS c_service
    FROM
        t_analyzes
    WHERE
        c_etat IS NULL 
    UNION SELECT 
        c_etat,
        c_patient,
        100 AS c_prix,
        'CONSULTATION' AS c_service
    FROM
        t_consultations
    WHERE
        c_etat IS NULL 
    UNION SELECT 
        c_etat,
        c_patient,
        DATEDIFF(NOW(), c_datee) * c_prix,
        'RESERVATION' AS c_service
    FROM
        t_reservations
            JOIN
        t_chambres ON t_chambres.c_id = t_reservations.c_chambre
    WHERE
        c_etat IS NULL;

INSERT INTO t_admins ( c_nom, c_prenom, c_email, c_adresse, c_telephone, c_password) VALUES ('A','A','A','A','A','A');
INSERT INTO t_comptables ( c_nom, c_prenom, c_email, c_adresse, c_telephone, c_password) VALUES ('C','C','C','C','C','C');
INSERT INTO t_docteurs ( c_nom, c_prenom, c_email, c_adresse, c_telephone, c_password) VALUES ('D','D','D','D','D','D');
INSERT INTO t_infirmiers ( c_nom, c_prenom, c_email, c_adresse, c_telephone, c_password) VALUES ('I','I','I','I','I','I');
INSERT INTO t_labos ( c_nom, c_prenom, c_email, c_adresse, c_telephone, c_password) VALUES ('L','L','L','L','L','L');
INSERT INTO t_pharmaciens ( c_nom, c_prenom, c_email, c_adresse, c_telephone, c_password) VALUES ('P','P','P','P','P','P');
INSERT INTO t_receptions ( c_nom, c_prenom, c_email, c_adresse, c_telephone, c_password) VALUES ('R','R','R','R','R','R');
INSERT INTO t_blocs ( c_nom) VALUES ('BLOC1'),('BLOC2'),('BLOC3'),('BLOC4');
INSERT INTO t_chambres (c_prix,c_bloc) VALUES (150,1),(300,2),(100,3),(400,4);

DELIMITER $$
CREATE TRIGGER `t_detail_factures_AINS` AFTER INSERT ON `t_detail_factures` FOR EACH ROW
BEGIN

	SET @PATIENT:=(SELECT c_patient FROM t_factures WHERE c_id=new.c_facture);
	IF (new.c_service='ANALYZE') THEN
		UPDATE t_analyzes SET c_etat=1 WHERE c_patient=@PATIENT;
	ELSEIF (new.c_service='RESERVATION') THEN
		UPDATE t_reservations SET c_etat=1 ,c_dates=NOW() WHERE c_patient=@PATIENT;
	ELSEIF (new.c_service='CONSULTATION') THEN
		UPDATE t_consultations SET c_etat=1 WHERE c_patient=@PATIENT;
	END IF;
END$$

COMMIT;